package database;
import golf.*;
import golf.Character;
import golf.Room.Game;
import golf.Room.Square;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;



public class DAO {
	private static DAO dao = null;
	public static DAO getInstance(){
		if( dao == null ){
			dao = new DAO();
		}
		return dao;
	}
	
	public DAO(){
		if( !connect("db", "db") )
		{
			System.out.println("연결되지 않음");
		}else{
			System.out.println("DAO 연결 완료!!");
		}
	}
	
	public Connection con;
	
	public Connection getConnection(){
		return con;
	}
	
	public boolean connect(String id, String pw){
		boolean ret = false;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", id, pw);
			ret = true;
		} catch (ClassNotFoundException e) {
			System.out.println("class not found");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("sql exception");
			
			e.printStackTrace();
		}
		return ret;
	}
	
	public boolean close(){
		boolean ret = false;
		try{
			con.close();
			System.out.println("close ok");
			ret = true;
		}catch(SQLException e){
			System.out.println("break;");
		}
		return ret;
	}
	
	public String getQuery(String filename){
		FileReader fr;
		String ret = null;
		try {
			fr = new FileReader(filename);
			ret = fr.toString();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return ret;
	}
	
	public boolean join( Account account ){
		boolean ret = false;
		try{
			String sql = "insert into ACCOUNT(ID,JUMIN,NAME,PASSWORD) values(?,?,?,?)";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, account.getId());
			pstmt.setString(2, account.getJumin());
			pstmt.setString(3, account.getName());
			pstmt.setString(4, account.getPassword());
			pstmt.execute();
			System.out.println("회원가입 완료");
			ret = true;
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.join() 실패!!");
		}
		return ret;
	}
	
	public Account getAccount(String id){
		Account ret = null;
		try{
			String sql = "select * from ACCOUNT where ID=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				ret = new Account(rs);
			}
			else{
				System.out.println("데이터가 없습니다");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getAccount() 실패!!");
		}
		return ret;
	}
	
	public Account login(String id, String pw){
		Account account = null;
		account = getAccount(id);
		if( account != null && account.getPassword() != null && account.getPassword().compareTo(pw) == 0 ){
			System.out.println(account.getId() + "님은 로그인에 성공했습니다.");
			return account;
		}else{
			System.out.println(id + "님은 로그인에 실패했습니다..");
		}
		
		return null;
	}
	
	public Server getServer( int serverNo ){
		Server server = null;
		try{
			String sql = "select * from SERVER where SVNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, serverNo);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				server = new Server(rs);
				System.out.println("server 읽기 성공");
			}
			else{
				System.out.println("server가 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getServer() 실패!!");
		}
		
		return server;
	}
	
	public Server[] getServerList(){
		ArrayList<Server> arr = new ArrayList<Server>();
		Server[] servers = null;
		Server serv = null;
		try{
			String sql = "select * from SERVER";
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				serv = new Server(rs);
				arr.add(serv);
			}
			if( arr.size() <= 0 ){
				System.out.println("server가 존재하지 않습니다.");
			}
			else{
				servers = new Server[arr.size()];
				arr.toArray(servers);
				System.out.println("server 목록 읽기 성공");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getServerList() 실패!!");
		}
		
		return servers;
	}
	
	public Channel getChannel(int channelNo){
			
		Channel channel = null;
		try{
			String sql = "select * from VIEW_CHANNEL where CHNNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, channelNo);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				channel = new Channel(rs);
				System.out.println("channel 읽기 성공");
			}
			else{
				System.out.println("channel이 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getChannel() 실패!!");
		}
		
		return channel;
	}
	
	public Channel[] getChannelList(int serverNo ){
		ArrayList<Channel> arr = new ArrayList<Channel>();
		Channel[] channels = null;
		Channel chn = null;
		try{

			String sql = "select * from VIEW_CHANNEL WHERE SVNO=?";		
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, serverNo);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				chn = new Channel(rs);
				arr.add(chn);
			}
			if( arr.size() <= 0 ){
				System.out.println("channel이 존재하지 않습니다.");
			}
			else{
				channels = new Channel[arr.size()];
				arr.toArray(channels);
				System.out.println("channel 목록 읽기 성공");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getChannelList() 실패!!");
		}
		
		return channels;
	}
	
	public boolean joinChannel( int channelNo ){
		boolean ret = false;
		try{
			String sql = "update CHANNEL set CHNNOWENTPERNO=CHNNOWENTPERNO+1 where CHNNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, channelNo);
			pstmt.execute();
			System.out.println("채널참가 완료");
			ret = true;
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.joinChannel() 실패!!");
		}
		return ret;
	}

	public Hole[] getHoleList(int courseNo){
		
		ArrayList<Hole> arr = new ArrayList<Hole>();
		Hole[] holes = null;
		Hole hole = null;
		try{
			String sql = "select h.*, c.CDNM DIFFICULTY from HOLE h, CODE c " +
				"where CRSNO=? and h.HOLEDIFFICDCLASSIFY=c.CDCLASSIFY and h.HOLEDIFFICD=c.CD";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, courseNo);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				hole = new Hole(rs);
				arr.add(hole);
			}
			rs.close();
			pstmt.close();
			if( arr.size() <= 0 ){
				System.out.println("room이 존재하지 않습니다.");
			}
			else{
				holes = new Hole[arr.size()];
				arr.toArray(holes);
				System.out.println("room 목록 읽기 성공");
			}

			
		} catch (SQLException e) {
			System.out.println("DAO.getRoomList() 실패!!");
		}
		
		return holes;
		
		
		
		
	}
	public Course[] getCourseList(){
		ArrayList<Course> arr = new ArrayList<Course>();
		Course[] courses = null;
		Course course = null;
		try{
			String sql = "select crs.*, c.CDNM DIFFICULTY from COURSE crs, CODE c " +
				"where crs.CRSDIFFICDCLASSIFY=c.CDCLASSIFY and crs.CRSDIFFICD=c.CD";
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				course = new Course(rs);
				arr.add(course);
			}
			rs.close();
			pstmt.close();
			if( arr.size() <= 0 ){
				System.out.println("course가 존재하지 않습니다.");
			}
			else{
				courses = new Course[arr.size()];
				arr.toArray(courses);
				System.out.println("course 목록 읽기 성공");
			}

			
		} catch (SQLException e) {
			System.out.println("DAO.getCourseList() 실패!!");
		}
		
		return courses;
	}
	
	public Course getCourse(int courseNo){
		Course course = null;
		String sql = "select crs.*, c.CDNM DIFFICULTY from COURSE crs, CODE c " +
				"where CRSNO=? and crs.CRSDIFFICDCLASSIFY=c.CDCLASSIFY and crs.CRSDIFFICD=c.CD";
		try{
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, courseNo);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				course = new Course(rs);
			}
			else{
				System.out.println("course가 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getCourse() 실패!!");
		}
		return course;
	}
	public Game getGame(int roomNo){
		Game game = null;
		String sql = "select g.*, c.CDNM GAMETYPENAME from GAME g, CODE c " +
				"where ROOMNO=? and g.GAMETYPECDCLASSIFY=c.CDCLASSIFY and g.GAMETYPECD=c.CD";
		try{
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				game = new Game(rs);
			}
			else{
				System.out.println("room이 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGame() 실패!!");
		}
		return game;
	}
	public Square getSquare(int roomNo){
		Square square = null;
		String sql = "select * from SQUARE where ROOMNO=?";
		
		try{
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				square = new Square(rs);
			}
			else{
				System.out.println("room이 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getSquare() 실패!!");
		}
		return square;
	}
	
	public Room getRoom(int roomNo){
		Room room = null;
		try{
			String sql = "select * from VIEW_ROOM where ROOMNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				room = new Room(rs);
			}
			else{
				System.out.println("room이 존재하지 않습니다.");
			}
			rs.close();
			pstmt.close();
			
			if( room.getRoomType().compareTo(Room.ROOMTYPE_GAME) == 0){
				room.setGame(getGame(room.getNo()));
			}
			else{
				room.setSquare(getSquare(room.getNo()));
			}
			
		} catch (SQLException e) {
			System.out.println("DAO.getRoom() 실패!!");
		}
		
		return room;
	}
	public Room[] getRoomList( int channelNo ){
		ArrayList<Room> arr = new ArrayList<Room>();
		Room[] rooms = null;
		Room room = null;
		try{
			String sql = "select * from VIEW_ROOM where CHNNO=? AND FINISHED LIKE 'N'";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, channelNo);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				room = new Room(rs);
				arr.add(room);
			}
			rs.close();
			pstmt.close();
			if( arr.size() <= 0 ){
				System.out.println("room이 존재하지 않습니다.");
			}
			else{
				rooms = new Room[arr.size()];
				arr.toArray(rooms);
				System.out.println("room 목록 읽기 성공");
			}

			
		} catch (SQLException e) {
			System.out.println("DAO.getRoomList() 실패!!");
		}
		
		return rooms;
	}
	/*
	 * CHARNO	NUMBER(38,0)
ROOMNO	NUMBER(38,0)
PLAYERPLAYYN	CHAR(1 BYTE)
PLAYERNOWPOS	NUMBER(38,0)
PLAYERNOWSTROKENO	NUMBER(38,0)
PLAYERSKINENZI	NUMBER(38,0)
PLAYERWINCNT	NUMBER(38,0)
	 */
	public int joinRoom( int charNo, int roomNo, String password ){
		int ret = 0;
		String playYN = "N";
		Room room = getRoom( roomNo );
		
		if( room.getLimit_person_no() <= room.getNow_person_no() ){
			return Room.ROOM_IS_FULL;
		}
		else if( room.getPassword() != null && room.getPassword().compareTo(password) != 0 ){
			return Room.PASSWORD_NOT_CORRECT;
		}
		try{
			String sql  = "INSERT INTO PLAYER (CHARNO, ROOMNO, CREATORYN, PLAYERPLAYYN) VALUES (?, ?, ?, ?)";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			pstmt.setInt(2, roomNo);
			pstmt.setString(3, "N");
			//방 타입이 게임이고 아직 진행중이 아니라면 플레이어는 참여할 수 있음
			if( room.getRoomType().compareTo("게임") == 0 && room.getGame().getPlayYN() == 'N' 
				&&room.getNow_person_no() < room.getLimit_person_no()){
				playYN = "Y";
			}
			pstmt.setString(4, playYN);
			
			pstmt.execute();
			System.out.println(roomNo + "번 방 참가 성공!");
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.joinRoom() 실패!!");
		}
		
		return ret;
	}
	
	public boolean leaveRoom( int roomNo, int charNo ){
		boolean ret = false;
		String sql = "delete from PLAYER where ROOMNO=? AND CHARNO=?";
		try {
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			pstmt.setInt(2, charNo);
			pstmt.execute();
			ret = true;
			System.out.println("방 나가기 성공");
		} catch (SQLException e) {
			System.out.println("방 나가기 실패!!");
			e.printStackTrace();
		}
		return ret;
		
	}

	public Character getCharacter( int charNo ){
		Character character = null;
		try{
			String sql = "select * from CHARACTER where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				character = new Character(rs);
				System.out.println("캐릭터 읽기 성공");
			}
			else{
				System.out.println(charNo+"에 해당하는 character가 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getCharacter() 실패!!");
		}
		
		return character;
		
	}
	public Character[] getCharacterList( String accountID, int serverNo){
		Character[] characters = null;
		Character character = null;
		
		ArrayList<Character> arr = new ArrayList<Character>();
		try{
			String sql = "select c.* " +
			"from CHARACTER c, SERVERACCOUNT sa "+
			"where sa.SVNO=? and sa.ID=? and " + 
			"(sa.CHARNO1=c.CHARNO or sa.CHARNO2=c.CHARNO or sa.CHARNO3=c.CHARNO)";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, serverNo);
			pstmt.setString(2, accountID);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				character = new Character(rs);
				arr.add(character);
				
			}
			if( arr.size() <= 0 ){
				System.out.println("character가 존재하지 않습니다.");
			}
			else{
				characters = new Character[arr.size()];
				arr.toArray(characters);
				System.out.println("characterer 목록 읽기 성공");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getCharacterList() 실패!!");
		}
		
		return characters;
	}
	public String createCharacter( int serverNo, String accountID, String charName, int modelNo ){
		String ret = null;
		
		//SVNO, ID, CHARNM, MODELNO;
		String sql = "select FUNCTION_CREATE_CHARACTER(?, ?, ?, ?) RESULT from dual";
		PreparedStatement pstmt;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, serverNo);
			pstmt.setString(2, accountID);
			pstmt.setString(3, charName);
			pstmt.setInt(4, modelNo);
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() ){
				ret = rs.getString("RESULT");
				System.out.println(ret);
			}
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
		return ret;
	}
	public boolean deleteCharacter( int charNo ){
		boolean ret = false;
		
		try{
			String sql = "delete from CHARACTER where CHARNO=?";

			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			pstmt.execute();
			pstmt.close();
			System.out.println("캐릭터 삭제 완료");
			ret = true;
		} catch (SQLException e) {
			System.out.println("DAO.deleteCharacter() 실패!!");
		}
		return ret;
	}
	
	public Model[] getModelList(){
		Model[] models = null;
		ArrayList<Model> arr = new ArrayList<Model>();
		Model model = null;
		try{
			String sql = "select * from MODEL";
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				model = new Model(rs);
				arr.add(model);
			}
			if( arr.size() > 0 ){
				models = new Model[arr.size()];
				arr.toArray(models);
				System.out.println("모델을 성공적으로 가져왔습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getModelList() 실패!!");
		}
		
		return models;
	}
	public Model getModel( int modelNo ){
		Model model = null;
		try{
			String sql = "select * from MODEL where MODELNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, modelNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				model = new Model(rs);
				System.out.println("model 읽기 성공");
			}
			else{
				System.out.println(modelNo+"에 해당하는 model이 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getModel() 실패!!");
		}
		
		return model;
	}
	public Character.CharacterGrade getCharacterGrade( int gradeNo ){
		Character.CharacterGrade grade = null;
		try{
			String sql = "select * from CHARACTERGRADE where CHARGRADENO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, gradeNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				grade = new Character.CharacterGrade(rs);
				System.out.println("grade 읽기 성공");
			}
			else{
				System.out.println(gradeNo+"에 해당하는 grade가 존재하지 않습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGrade() 실패!!");
		}
		
		return grade;
	}
	
	public ChatMsg[] getChatMsg( ChatMsg cm ){
		ArrayList<ChatMsg> arr = new ArrayList<ChatMsg>();
		ChatMsg[] chatMsgs = null;
		ChatMsg chatMsg = null;
		try{
			String sql = "SELECT * FROM TABLE(FUNCTION_FETCH_CHAT(?,?,?,?,?))";
						  
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, cm.getServerNo());
			pstmt.setInt(2, cm.getChatNo());
			pstmt.setInt(3, cm.getSenderNo());	//recvNo인데 귀찮아서 걍 이렇게 갑시다!
			pstmt.setInt(4, cm.getGuildNo());
			pstmt.setInt(5, cm.getRoomNo());
			
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				chatMsg = new ChatMsg();
				chatMsg.setChatCd(rs.getString("CHATCD"));
				chatMsg.setChatCdClassify(rs.getString("CHATCDCLASSIFY"));
				chatMsg.setChatContent(rs.getString("CHATCONTENT"));
				chatMsg.setSenderNo(rs.getInt("SENDERCHARNO"));
				chatMsg.setServerNo(rs.getInt("SVNO"));
				chatMsg.setChatNo(rs.getInt("CHATNO"));
				chatMsg.setSenderName(rs.getString("CHARNM"));
				arr.add(chatMsg);
			}
			if( arr.size() <= 0 ){
				//System.out.println("채팅 메시지가 존재하지 않습니다.");
			}
			else{
				chatMsgs = new ChatMsg[arr.size()];
				arr.toArray(chatMsgs);
				System.out.printf("채팅 메시지 %d개 읽기 성공\n", arr.size());
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println(e.toString());
			System.out.println("DAO.getChatMsg() 실패!!");
		}
		return chatMsgs;
	}

	public boolean insertIntoChatTable (ChatMsg msg) {
		try {
		String sql = "INSERT INTO CHAT(CHATNO, SVNO, SENDERCHARNO, CHATCONTENT, CHATCDCLASSIFY, CHATCD) values(CHAT_seq.NEXTVAL,?,?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, msg.getServerNo());
		pstmt.setInt(2, msg.getSenderNo());
		pstmt.setString(3, msg.getChatContent());
		pstmt.setString(4, msg.getChatCdClassify());
		pstmt.setString(5, msg.getChatCd());
		
		pstmt.execute();
		//System.out.println("CHAT table 메시지 삽입 완료");
		return true;
		} catch (SQLException e) {
			System.out.println("DAO.insertIntoChatTable() 실패!!");
			System.out.println(e.toString());
			return false;
		}
	}
	
	public boolean addChatMsg( ChatMsg msg ){
		boolean ret = false;
		try{
			String sql="SELECT * FROM CHAT";
			PreparedStatement pstmt = con.prepareStatement(sql);
			if(msg.getChatCd().compareTo("CT001") == 0) {	//방챗
				if(insertIntoChatTable(msg)) {
					sql = "INSERT INTO ROOMCHAT(CHATNO, RECVROOMNO) values(CHAT_seq.CURRVAL, ?)";
					pstmt = con.prepareStatement(sql);
					pstmt.setInt(1, msg.getRoomNo());
					pstmt.execute();
					//System.out.println("ROOM CHAT 삽입 완료");
				}
			} else if(msg.getChatCd().compareTo("CT002") == 0) {	//귓속말
				sql = "SELECT CHARNO FROM DB.CHARACTER WHERE CHARNM = '" + msg.getRecvName() + "'";
				System.out.println("sql : " + sql);
				pstmt = con.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				//System.out.println("캐릭터명 알아오기 완료");
				if(rs.next()) {
					if(insertIntoChatTable(msg)) {
						//System.out.println("귓속말 삽입 준비  ");
						sql = "INSERT INTO WHISPERCHAT(CHATNO, RECVCHARNO) values(CHAT_seq.CURRVAL, ?)";
						pstmt = con.prepareStatement(sql);
						pstmt.setInt(1, rs.getInt("CHARNO"));	//RECVNO가 아니고 RECVNAME...쿼리또 날리자." +
						pstmt.execute();
						//System.out.println("귓속말 완료");
						sendNotifyMsg(msg, msg.getRecvName() + " >> " + msg.getChatContent());
					}
				} else {
					sendNotifyMsg(msg, "귓속말을 보낼 수 없습니다.");
				}
				
			} else if(msg.getChatCd().compareTo("CT003") == 0) {	//길드채팅
				if(msg.getGuildNo() != -1) {
					//System.out.println("길드채팅 넣기 준비" + msg.getGuildNo());
					if(insertIntoChatTable(msg)) {
						sql = "INSERT INTO GUILDCHAT(CHATNO, RECVGUILDNO) values(CHAT_seq.CURRVAL, ?)";
						pstmt = con.prepareStatement(sql);
						pstmt.setInt(1, msg.getGuildNo());
						pstmt.execute();
						//System.out.println("길드채팅 삽입 완료");
					}
				} else {
					sendNotifyMsg(msg, "가입된 길드가 없습니다.");
				}
			}
				ret = true;
				pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.addChatMsg() 실패!!");
			System.out.println(e.toString());
		}
		return ret;
	}
	
	public void sendNotifyMsg(ChatMsg msg, String noti) {
		String sql;
		PreparedStatement pstmt;
		msg.setChatContent(noti);
		msg.setChatCd("CT002");
		if(insertIntoChatTable(msg)) {
			sql = "INSERT INTO WHISPERCHAT(CHATNO, RECVCHARNO) values(CHAT_seq.CURRVAL, ?)";
			try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, msg.getSenderNo());	//자기 자신에게 귓속말 보내기.
			pstmt.execute();
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
	}
	
	public ProductService getProductService(int psno){
		ProductService ps = null;
		try{
			String sql = "select * from VIEW_PS_LIST where PSNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, psno);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				ps = new ProductService(rs);
				System.out.println("요금상품을 성공적으로 가져왔습니다.");
			}
			else{
				System.out.println("상품번호 ["+psno+"]에 해당하는 상품이 존재하지 않습니다.");
			}
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getProductService() 실패!!");
		}
		
		return ps;
	}
	
	public ProductService[] getProductServiceList(){
		ProductService[] products = null;
		ArrayList<ProductService> arr = new ArrayList<ProductService>();
		ProductService ps = null;
		try{
			String sql = "select * from VIEW_PS_LIST";
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				ps = new ProductService(rs);
				arr.add(ps);
			}
			if( arr.size() > 0 ){
				products = new ProductService[arr.size()];
				arr.toArray(products);
				System.out.println("요금상품 목록을 성공적으로 가져왔습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getProductServiceList() 실패!!");
		}
		
		return products;
	}

	
	public boolean setProductService(String accountId, int psNo){
		boolean ret = false;
		try{
			String sql = "update ACCOUNT set PSNO=?, PSBUYDATE=SYSDATE where ID=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			if( psNo != 0)
				pstmt.setInt(1, psNo);
			else
				pstmt.setNull(1, java.sql.Types.INTEGER);
			pstmt.setString(2, accountId);
			pstmt.execute();
			System.out.println("in DAO.buyProductService() 요금구입 완료");
			ret = true;
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.buyProductService() 실패!!");
		}
		return ret;
	}
	
	/*
	 * 캐릭터의 속성을 계산해서 가져온다(VIEW로부터)
	 * 
	 */
	public Property getCharacterProperty(int charNo){
		Property property = null;
		try{
			String sql = "select * from VIEW_CHAR_PROPERTY where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				property = new Property(rs);
				System.out.println("캐릭터의 속성을 성공적으로 가져왔습니다.");
			}
			else{
				System.out.println("캐릭터번호 ["+charNo+"]에 해당하는 상품이 존재하지 않습니다.");
			}
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getProperty() 실패!!");
		}
		
		return property;
	}
	
	
	public Property getProperty(int propNo){
		Property property = null;
		try{
			String sql = "select * from PROPERTY where PROPNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, propNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				property = new Property(rs);
				System.out.println("속성을 성공적으로 가져왔습니다.");
			}
			else{
				System.out.println("속성번호 ["+propNo+"]에 해당하는 상품이 존재하지 않습니다.");
			}
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getProperty() 실패!!");
		}
		
		return property;
	}
	
	public Store[] getStoreList(){
		Store[] products = null;
		ArrayList<Store> arr = new ArrayList<Store>();
		Store ps = null;
		try{
			String sql = "select * from STORE";
			PreparedStatement pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				ps = new Store(rs);
				arr.add(ps);
			}
			if( arr.size() > 0 ){
				products = new Store[arr.size()];
				arr.toArray(products);
				System.out.println("상점 목록을 성공적으로 가져왔습니다.");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getStoreList() 실패!!");
		}
		
		return products;
	}
	
	
	public  Item[] getStoreSellingItemList(int storeNo){
		Item[] items = null;
		ArrayList<Item> arr = new ArrayList<Item>();
		Item item = null;
		try{
			String sql = "select * from VIEW_SHOP_ITEM_LIST where STORENO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, storeNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new Item(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new Item[arr.size()];
				arr.toArray(items);
				System.out.println("요금상품 목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getStoreSellingItems() 실패!!");
		}
		
		return items;
	}
	public String buyItem(int charNo, int itemNo){
		String result = null;
		PreparedStatement pstmt;
		try {
		
			String sql = "select FUNCTION_BUYITEM(? , ?) RESULT from dual";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			pstmt.setInt(2, itemNo);
			//pstmt.setInt(3, itemSeqNo);
			
			//System.out.println("CHARACTEROWNITEM테이블에 레코드 생성");
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() ){
				result = rs.getString("RESULT");
				System.out.println(result);
			}
			pstmt.close();
			rs.close();
		} catch (SQLException e) {
			System.out.println("DAO.buyItem() 실패!!");
		}
		return result;
		
	}
	public String transItem(int seqNo){
		String result = null;
		PreparedStatement pstmt;
		try {
		
			String sql = "select FUNCTION_BUYITEM(? , ?) RESULT from dual";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, seqNo);
			
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() ){
				result = rs.getString("RESULT");
				System.out.println(result);
			}
			pstmt.close();
			rs.close();
		} catch (SQLException e) {
			System.out.println("DAO.buyItem() 실패!!");
		}
		return result;
		
	}
	public String transItem(int No, String ID, int seqNo,int flag){
		String result = null;
		
		try {
			if(flag==1)
			{
				PreparedStatement pstmt;
				String sql = "select FUNCTION_TRANSITEM1(?, ?) RESULT from dual";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, No);
				pstmt.setInt(2, seqNo);
				ResultSet rs = pstmt.executeQuery();
				if( rs.next() ){
					result = rs.getString("RESULT");
					System.out.println(result);
				}
				rs.close();
				pstmt.close();
			}
			else if(flag==2)
			{
				PreparedStatement pstmt2;
				String sql2 = "select FUNCTION_TRANSITEM2(?, ?) RESULT from dual";
				pstmt2 = con.prepareStatement(sql2);
				pstmt2.setInt(1, No);
				pstmt2.setInt(2, seqNo);
				ResultSet rs2 = pstmt2.executeQuery();
				if( rs2.next() ){
					result = rs2.getString("RESULT");
					System.out.println(result);
				}
				rs2.close();
				pstmt2.close();
			}
			else if(flag==3)
			{
				PreparedStatement pstmt3;
				String sql3 = "select FUNCTION_TRANSITEM3(?,?,?) RESULT from dual";
				pstmt3 = con.prepareStatement(sql3);
				pstmt3.setInt(1, No);
				pstmt3.setString(2, ID);
				pstmt3.setInt(3, seqNo);
				ResultSet rs3 = pstmt3.executeQuery();
				if( rs3.next() ){
					result = rs3.getString("RESULT");
					System.out.println(result);
				}
				rs3.close();
				pstmt3.close();
			}
			else if(flag==4)
			{
				PreparedStatement pstmt4;
				String sql4 = "select FUNCTION_TRANSITEM4(?,?) RESULT from dual";
				pstmt4 = con.prepareStatement(sql4);
				pstmt4.setInt(1, No);
				pstmt4.setInt(2, seqNo);
				ResultSet rs4 = pstmt4.executeQuery();
				if( rs4.next() ){
					result = rs4.getString("RESULT");
					System.out.println(result);
				}
				rs4.close();
				pstmt4.close();
			}
			else if(flag==5)
			{
				PreparedStatement pstmt5;
				String sql5 = "select FUNCTION_TRANSITEM5(?,?) RESULT from dual";
				pstmt5 = con.prepareStatement(sql5);
				pstmt5.setInt(1, No);
				pstmt5.setInt(2, seqNo);
				ResultSet rs5 = pstmt5.executeQuery();
				if( rs5.next() ){
					result = rs5.getString("RESULT");
					System.out.println(result);
				}
				rs5.close();
				pstmt5.close();
			}
			
			
			
			
			//pstmt.setInt(3, itemSeqNo);
			
		} catch (SQLException e) {
			System.out.println("DAO.transItem() 실패!!");
		}
		return result;
		
	}
	public String wearItem(int charNo, int itemNo, int seqNo,int flag){
		String result = null;
		PreparedStatement pstmt;
		try {
		
			String sql = "select FUNCTION_WEARITEM(?, ?, ?, ?) RESULT from dual";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			pstmt.setInt(2, itemNo);
			pstmt.setInt(3, seqNo);
			pstmt.setInt(4, flag);
			//pstmt.setInt(3, itemSeqNo);
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() ){
				result = rs.getString("RESULT");
				System.out.println(result);
			}
			pstmt.close();
			rs.close();
		} catch (SQLException e) {
			System.out.println("DAO.wearItem() 실패!!");
		}
		return result;
		
	}
	public String repairItem(int charNo,int seqNo){
		String result = null;
		PreparedStatement pstmt;
		try {
				
				String sql = "select FUNCTION_REPAIRITEM(?, ?) RESULT from dual";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, charNo);
				pstmt.setInt(2, seqNo);
	
				//pstmt.setInt(3, itemSeqNo);
				ResultSet rs = pstmt.executeQuery();
				if( rs.next() ){
					result = rs.getString("RESULT");
					System.out.println(result);
				}
				pstmt.close();
				rs.close();
		} catch (SQLException e) {
			System.out.println("DAO.repairItem() 실패!!");
		}
		return result;
		
	}
	public String caddyItem(int charNo, int itemNo, int seqNo,int flag){
		String result = null;
		PreparedStatement pstmt;
		try {
				
				String sql = "select FUNCTION_CADDYITEM(?, ?, ?, ?) RESULT from dual";
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, charNo);
				pstmt.setInt(2, itemNo);
				pstmt.setInt(3, seqNo);
				pstmt.setInt(4, flag);
				//pstmt.setInt(3, itemSeqNo);
				ResultSet rs = pstmt.executeQuery();
				if( rs.next() ){
					result = rs.getString("RESULT");
					System.out.println(result);
				}
				pstmt.close();
				rs.close();
		} catch (SQLException e) {
			System.out.println("DAO.caddyItem() 실패!!");
		}
		return result;
		
	}
	public int getOwnEnzi(int charNo){
		int result=0; 
		PreparedStatement pstmt;
		try {
		
			String sql = "select ENZI from CHARACTER where CHARNO=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,charNo);
			ResultSet rs = pstmt.executeQuery();
			if( rs.next() ){
				result = rs.getInt("ENZI");
				System.out.println(result);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getOwnEnzi() 실패!!");
		}
		return result;
		
	}
	public OnItemList[] getWearItemList(int charNo){
		OnItemList[] items = null;
		ArrayList<OnItemList> arr = new ArrayList<OnItemList>();
		OnItemList item = null;
		try{
			
			String sql = "select * from VIEW_WEAR_ITEM_LIST where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new OnItemList(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new OnItemList[arr.size()];
				arr.toArray(items);
				System.out.println("착용아이템목록 을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getwearItems() 실패!!");
		}
		
		return items;
	}
	public OnItemList[] getCaddyItemList(int charNo){
		OnItemList[] items = null;
		ArrayList<OnItemList> arr = new ArrayList<OnItemList>();
		OnItemList item = null;
		try{
			
			String sql = "select * from VIEW_CADDY_ITEM_LIST where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new OnItemList(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new OnItemList[arr.size()];
				arr.toArray(items);
				System.out.println("캐디백목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getCaddyItems() 실패!!");
		}
		
		return items;
	}
	public RepairList[] getRepairList(int charNo){
		RepairList[] items = null;
		ArrayList<RepairList> arr = new ArrayList<RepairList>();
		RepairList item = null;
		try{
			
			String sql = "select * from VIEW_REPAIR_ITEM_LIST where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new RepairList(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new RepairList[arr.size()];
				arr.toArray(items);
				System.out.println("수리목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getrepairItems() 실패!!");
		}
		
		return items;
	}
	//인벤목록
	public ItemList[] getIvenItemList(int charNo){
		ItemList[] items = null;
		ArrayList<ItemList> arr = new ArrayList<ItemList>();
		ItemList item = null;
		try{
			
			String sql = "select * from VIEW_CHARACTER_ITEM_LIST where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new ItemList(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new ItemList[arr.size()];
				arr.toArray(items);
				System.out.println("인벤토리목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getInvenItems() 실패!!");
		}
		
		return items;
	}
	//락커룸 목록
	public ItemList[] getLockerItemList(int svno,String id){
		ItemList[] items = null;
		ArrayList<ItemList> arr = new ArrayList<ItemList>();
		ItemList item = null;
		try{
			
			String sql = "select * from VIEW_LOCKER_ITEM_LIST where SVNO=? and ID=?" ;
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, svno);
			pstmt.setString(2, id);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new ItemList(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new ItemList[arr.size()];
				arr.toArray(items);
				System.out.println("락커 목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getLokerItems() 실패!!");
		}
		
		return items;
	}
	public ItemList[] getVLockerItemList(String id){
		ItemList[] items = null;
		ArrayList<ItemList> arr = new ArrayList<ItemList>();
		ItemList item = null;
		try{
			
			String sql = "select * from VIEW_VLOCKER_ITEM_LIST where ID=?" ;
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new ItemList(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new ItemList[arr.size()];
				arr.toArray(items);
				System.out.println("가상락커 목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getLokerItems() 실패!!");
		}
		
		return items;
	}
	public ItemList[] getGuildItemList(int guildno){
		ItemList[] items = null;
		ArrayList<ItemList> arr = new ArrayList<ItemList>();
		ItemList item = null;
		try{
			
			String sql = "select * from VIEW_GLOCKER_ITEM_LIST where GUILDNO=? " ;
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, guildno);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				item = new ItemList(rs);
				arr.add(item);
			}
			if( arr.size() > 0 ){
				items = new ItemList[arr.size()];
				arr.toArray(items);
				System.out.println("길드락커 목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGuildItems() 실패!!");
		}
		
		return items;
	}
	public Guild[] getGuildListInServer(int serverNo){
		Guild[] guilds = null;
		ArrayList<Guild> arr = new ArrayList<Guild>();
		Guild guild = null;
		try{
			String sql = "select * from Guild where SVNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, serverNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				guild = new Guild(rs);
				arr.add(guild);
			}
			if( arr.size() > 0 ){
				guilds = new Guild[arr.size()];
				arr.toArray(guilds);
				System.out.println("요금상품 목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getStoreSellingItems() 실패!!");
		}
		
		return guilds;
	}
	
	public Guild getGuild( int guildNo ){
		Guild guild = null;
		try{
			String sql = "select * from GUILD where GUILDNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, guildNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				guild = new Guild(rs);
				System.out.println("길드원 정보를 성공적으로 가져왔습니다.");
			}
			else{
				System.out.println("길드번호 ["+guildNo+"]에 해당하는 길드가 존재하지 않습니다.");
			}
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGuild() 실패!!");
		}
		return guild;
	}
	public Guild.GuildMember[] getGuildMemberListInGuild(int guildNo){
		Guild.GuildMember[] members = null;
		ArrayList<Guild.GuildMember> arr = new ArrayList<Guild.GuildMember>();
		Guild.GuildMember member = null;
		try{
			String sql = "select * from VIEW_GUILD_MEMBER where GUILDNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, guildNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				member = new Guild.GuildMember(rs);
				arr.add(member);
			}
			if( arr.size() > 0 ){
				members = new Guild.GuildMember[arr.size()];
				arr.toArray(members);
				System.out.println("길드원 목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGuildMemberListInGuild() 실패!!");
		}
		
		return members;
	}
	
	public Guild.GuildMember getGuildMember( int charNo ){
		Guild.GuildMember mem = null;
		try{
			String sql = "select * from VIEW_GUILD_MEMBER where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				mem = new Guild.GuildMember(rs);
				System.out.println("길드원 정보를 성공적으로 가져왔습니다.");
			}
			else{
				System.out.println("캐릭터번호 ["+charNo+"]에 해당하는 길드멤버가 존재하지 않습니다.");
			}
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGuildMember() 실패!!");
		}
		return mem;
	}
	
	public Guild.GuildGrade getGuildGrade(int gradeNo){
		Guild.GuildGrade grade = null;
		try{
			String sql = "select * from GUILDGRADE where GUILDGRADENO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, gradeNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				grade = new Guild.GuildGrade(rs);
				System.out.println("길드등급 정보를 성공적으로 가져왔습니다.");
			}
			else{
				System.out.println("길드등급번호 ["+gradeNo+"]에 해당하는 등급이 존재하지 않습니다.");
			}
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGuildGrade() 실패!!");
		}
		return grade;
	}
	
	public String createGuild(String guildName, int serverNo, int charNo){
		String result = null;
		try{
			//GUILDNM, SVNO, CHARNO 
			String sql = "select FUNCTION_CREATEGUILD(?, ?, ?) RESULT from dual";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, guildName);
			pstmt.setInt(2, serverNo);
			pstmt.setInt(3, charNo);
			ResultSet rs = pstmt.executeQuery();
			if( rs.next() ){
				result = rs.getString("RESULT");
				System.out.println(result);
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.createGuild() 실패!!");
		}
		return result;
	}
	
	public String joinGuild(int guildNo, int serverNo, int charNo){
		String result = null;
		try{
			//GUILDNM, SVNO, CHARNO 
			String sql = "select FUNCTION_JOIN_GUILD(?, ?, ?) RESULT from dual";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, guildNo);
			pstmt.setInt(2, serverNo);
			pstmt.setInt(3, charNo);
			
			ResultSet rs = pstmt.executeQuery();
			if( rs.next() ){
				result = rs.getString("RESULT");
				System.out.println(result);
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.joinGuild() 실패!!");
		}
		return result;
		
	}
	
	public String withdrawFromGuild( int guildNo, int charNo){
		String result = null;
		try{
			//GUILDNM, SVNO, CHARNO 
			String sql = "select FUNCTION_WITHDRAW_GUILD(?, ?) RESULT from dual";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, guildNo);
			pstmt.setInt(2, charNo);
			
			ResultSet rs = pstmt.executeQuery();
			if( rs.next() ){
				result = rs.getString("RESULT");
				System.out.println(result);
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.withdrawFromGuild() 실패!!");
		}
		return result;
	}
	
	public boolean updateGuildMemberJob( int guildNo, int charNo, String job ){
		boolean ret = false;
			
		try{
			String sql = "select FUNCTION_CHG_GLD_MEM_JOB(?,?,?) RESULT from dual";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, guildNo);
			pstmt.setInt(2, charNo);
			pstmt.setString(3, job);
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() )
				System.out.println(rs.getString("RESULT"));
			pstmt.close();
			
			ret = true;
		} catch (SQLException e) {
			System.out.println("DAO.updateGuildMemberJob() 실패!!");
		}
		return ret;
	}
	
	
	public int createGame( int creatorNo, Room room ){
		int ret = 0;
		/*
		 * CREATE OR REPLACE FUNCTION FUNCTION_CREATE_ROOM
  (ROOMTYPE_IN IN VARCHAR2, CHANNELNO_IN IN NUMBER, ROOMNM_IN IN VARCHAR2, CREATORNO_IN IN VARCHAR2, PASSWORD_IN IN VARCHAR2,
  GAMETYPE_IN IN VARCHAR2 DEFAULT NULL, LVLLIMIT_IN NUMBER DEFAULT NULL, PLAYERCNTLIMIT_IN NUMBER DEFAULT NULL,
  CRSNO_IN IN NUMBER DEFAULT NULL, HOLECNT_IN IN NUMBER DEFAULT NULL, ENZI_IN IN NUMBER DEFAULT NULL)
		 */
		String sql;
		if( room.getRoomType().compareTo(Room.ROOMTYPE_SQUARE)==0){//광장	
			try {
				sql = "SELECT FUNCTION_CREATE_ROOM(?,?,?,?,?) FROM DUAL";
				PreparedStatement pstmt = con.prepareStatement(sql);
				pstmt.setString(1, room.getRoomType());
				pstmt.setInt(2, room.getChnNo());
				pstmt.setString(3, room.getName());
				pstmt.setInt(4, creatorNo);
				pstmt.setString(5, room.getPassword());
				
				ResultSet rs = pstmt.executeQuery();
				if( rs.next() )
					System.out.println(rs.getInt("RESULT") + "번 광장이 생성되었습니다.");
				ret = rs.getInt("RESULT");
				pstmt.close();
			} catch (SQLException e) {
				System.out.println("광장 생성 도중 에러가 발생했습니다.");
				e.printStackTrace();
			}
		}else{ //게임
			try {
				sql = "SELECT FUNCTION_CREATE_ROOM(?,?,?,?,?,?,?,?,?,?,?) RESULT FROM DUAL";
				PreparedStatement pstmt = con.prepareStatement(sql);
				pstmt.setString(1, room.getRoomType());
				pstmt.setInt(2, room.getChnNo());
				pstmt.setString(3, room.getName());
				pstmt.setInt(4, creatorNo);
				pstmt.setString(5, room.getPassword());
				pstmt.setString(6, room.getGame().getGame_type());
				pstmt.setInt(7, room.getLimit_level());
				pstmt.setInt(8, room.getLimit_person_no());
				pstmt.setInt(9, room.getGame().getCource_no());
				pstmt.setInt(10, room.getGame().getHole_cnt());
				pstmt.setInt(11, room.getGame().getEnzi());
				ResultSet rs = pstmt.executeQuery();
				if( rs.next() )
					System.out.println(rs.getInt("RESULT") + "번 게임방이 생성되었습니다.");
				ret = rs.getInt("RESULT");
				pstmt.close();
			} catch (SQLException e) {
				System.out.println("광장 생성 도중 에러가 발생했습니다.");
				e.printStackTrace();
			}
		}
		
		return ret;
	}

	public Player getPlayer( int roomNo, int charNo ){
		Player player = null;
		try{
			String sql = "select * from PLAYER where ROOMNO=? AND CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			pstmt.setInt(2, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.next()){
				player = new Player(rs);
				System.out.println("플레이어 정보를 성공적으로 가져왔습니다.");
			}
			else{
				System.out.println("캐릭터번호 ["+charNo+"]에 해당하는 플레이어가 존재하지 않습니다.");
			}
			
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getPlayer() 실패!!");
		}
		return player;
	}
	
	public Player[] getPlayerList( int roomNo ){
		ArrayList<Player> arr = new ArrayList<Player>();
		Player[] players = null;
		Player player = null;
		try{
			String sql = "select * from PLAYER where ROOMNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				player = new Player(rs);
				arr.add(player);
			}
			if( arr.size() <= 0 ){
				System.out.println("player가 존재하지 않습니다.");
			}
			else{
				players = new Player[arr.size()];
				arr.toArray(players);
				System.out.println("player 목록 읽기 성공");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getPlayerList() 실패!!");
		}
		
		return players;
	}
	
	
	//게임에서 플레이어마다 1타수씩 친다.
	//RETURN값 설명
	//false : 더이상 홀을 진행할 수 없을 때(홀에 공을 집어넣었을 때)
	//true : 1타 쳤을 때.(아직 공을 못집어넣음.)
	public boolean playerOneShot( int charNo, int roomNo, int holeNo ){
		boolean ret = false;
		int result = 0;
		try{
			String sql = "select FUNCTION_ONE_SHOT(?,?,?) RESULT from dual";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			pstmt.setInt(2, roomNo);
			pstmt.setInt(3, holeNo);
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() )
				result = rs.getInt("RESULT");
				if(result == 1){
					System.out.println(charNo+"번 플레이어, 나이스샷~~");
					ret = true;
				}else{
					System.out.println(charNo+"번 플레이어는 이미 홀을 끝냈습니다.");
				}
				
			pstmt.close();
		
		} catch (SQLException e) {
			System.out.println("DAO.playerOneShot() 실패!!");
		}
		return ret;
		
	}
	
	public String resultHole(int roomNo, String gameType){
		String ret = null;
		try{
			String sql = "select FUNCTION_RESULT_HOLE(?,?) RESULT from dual";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			pstmt.setString(2, gameType);
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() ){
				ret = rs.getString("RESULT");
				System.out.println(ret);
			}
			pstmt.close();
		
		} catch (SQLException e) {
			System.out.println("DAO.resultHole() 실패!!");
		}
		return ret;
	}
	
	public boolean finishRoom( int roomNo ){
		boolean ret = false;
		try{
			String sql = "update ROOM set FINISHED='Y' where ROOMNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			pstmt.execute();
			System.out.println("방 종료 완료");
			ret = true;
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.finishRoom() 실패!!");
		}
		return ret;
	}
	
	public boolean startGame( int roomNo ){
		boolean ret = false;
		try{
			String sql = "update GAME set GAMEPLAYYN='Y' where ROOMNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			pstmt.execute();
			System.out.println("게임이 시작되었다!!");
			ret = true;
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.startGame() 실패!!");
		}
		return ret;
	}
	/*
	public Player[] getPlayerStatusPerHole( int roomNo ){
		Guild.GuildMember[] members = null;
		ArrayList<Guild.GuildMember> arr = new ArrayList<Guild.GuildMember>();
		Guild.GuildMember member = null;
		try{
			String sql = "select * from VIEW_GUILD_MEMBER where GUILDNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, guildNo);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				member = new Guild.GuildMember(rs);
				arr.add(member);
			}
			if( arr.size() > 0 ){
				members = new Guild.GuildMember[arr.size()];
				arr.toArray(members);
				System.out.println("길드원 목록을 성공적으로 가져왔습니다.");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getGuildMemberListInGuild() 실패!!");
		}
		
		return members;
	}
	*/
	
	public int getScore( int roomNo, int charNo ){
		int score = 0;
		try{
			String sql = "select SCORE from SCORECARD where ROOMNO=? AND CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, roomNo);
			pstmt.setInt(2, charNo);
			ResultSet rs = pstmt.executeQuery();
			
			if( rs.next() ){
				score = rs.getInt("SCORE");
				System.out.println("score : " + score);
			}
			pstmt.close();
		
		} catch (SQLException e) {
			System.out.println("DAO.getScore() 실패!!");
		}
		return score;
	}
	public boolean setCharacterTired(int newTired, int charNo) {
		try {
			String sql = "UPDATE CHARACTER SET tired = ? WHERE CHARNO = ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, newTired);
			pstmt.setInt(2, charNo);
			pstmt.execute();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("setCharacterTired() 실패");
			System.out.println(e);
		}
		return true;
	}
	
	public boolean setCharacterPassTest(int charNo) {
		try {
			String sql = "UPDATE CHARACTER SET PASSTEST = 'Y' WHERE CHARNO = ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			pstmt.execute();
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("setCharacterPassTest() 실패");
			System.out.println(e);
		}
		return true;
	}
	
	public boolean increaseProperty(int charNo, Property prop){
		boolean ret = false;
		try {
			String sql = "select FUNCTION_INCREASE_STATUS(?,?,?,?,?) RESULT from DUAL";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			pstmt.setInt(2, prop.getPower());
			pstmt.setInt(3, prop.getImpact());
			pstmt.setInt(4, prop.getTechnique());
			pstmt.setInt(5, prop.getStamina());
			
			ResultSet rs = pstmt.executeQuery();
			if( rs.next() ){
				String result = rs.getString("RESULT");
				System.out.println(result);
				if(result.compareTo("스탯증가완료")==0){
					ret=true;
				}
			}
			pstmt.close();
			System.out.println("increaseProperty() 완료!");
		} catch (SQLException e) {
			System.out.println("increaseProperty() 실패");
			System.out.println(e);
		}
		return ret;
	}
	
	public ScoreCard[] getScoreCardList(int charNo){
		ArrayList<ScoreCard> arr = new ArrayList<ScoreCard>();
		ScoreCard[] scores = null;
		ScoreCard score = null;
		try{
			String sql = "select * from SCORECARD where CHARNO=?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, charNo);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				score = new ScoreCard(rs);
				arr.add(score);
			}
			if( arr.size() <= 0 ){
				System.out.println("scorecard가 존재하지 않습니다.");
			}
			else{
				scores = new ScoreCard[arr.size()];
				arr.toArray(scores);
				System.out.println("scorecard 목록 읽기 성공");
			}
			pstmt.close();
		} catch (SQLException e) {
			System.out.println("DAO.getScoreCardList() 실패!!");
		}
		
		return scores;
	}
}
